#region << 版 本 注 释 >>
/*----------------------------------------------------------------
 * 版权所有 (c) 2025   保留所有权利。
 * CLR版本：4.0.30319.42000
 * 机器名称：DESKTOP-GLJ2Q8J
 * 公司名称：
 * 命名空间：Npoi.ExcelOperate
 * 唯一标识：f929fdaa-f963-413a-89ce-74d9b6b87a39
 * 文件名：ExcelOperate
 * 当前用户域：DESKTOP-GLJ2Q8J
 * 
 * 创建者： Guo_79991
 * 电子邮箱：799919859@qq.com
 * 创建时间：2025/10/9 13:38:38
 * 版本：V1.0.0
 * 描述：
 *
 * ----------------------------------------------------------------
 * 修改人：
 * 时间：
 * 修改说明：
 *
 * 版本：V1.0.1
 *----------------------------------------------------------------*/
#endregion << 版 本 注 释 >>

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Windows.Forms;


namespace Npoi.ExcelOperate
{
    /// <summary>
    /// Excel操作类,读取Excel文件,获取所有数据.该类为抽象类,不能实例化,只能被继承
    /// </summary>
    /// <summary>
    /// Excel操作类,读取Excel文件,获取所有数据.该类为抽象类,不能实例化,只能被继承
    /// </summary>
    public abstract class ExcelOperator
    {

        /// <summary>
        /// 源文件路径
        /// </summary>
        protected string FilePath { get; set; }
        /// <summary>
        /// 文件保存路径
        /// </summary>
        protected string SavePath { get; set; }

        public string GetFilePath()
        {
            return this.FilePath;
        }

        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <returns>工作簿接口对象</returns>
        public IWorkbook ReadExcel()
        {
            IWorkbook workbook = null;
            if (!File.Exists(FilePath))
            {
                MessageBox.Show(FilePath + "该文件未能找到,请检查该读入文件是否存在");
                return null;
            }
            string extension = System.IO.Path.GetExtension(FilePath);
            try
            {
                FileStream fs = File.OpenRead(FilePath);
                if (extension.Equals(".xls"))//2007以下
                {
                    workbook = new HSSFWorkbook(fs);
                }
                if (extension.Equals(".xlsx"))//2007以上
                {
                    workbook = new XSSFWorkbook(fs);
                }
                fs.Close();
                return workbook;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return null;
            }
        }

        /// <summary>
        /// 获取所有数据
        /// </summary>
        /// <typeparam name="T">泛型类型</typeparam>
        /// <param name="action">委托传入具体读取逻辑</param>
        /// <returns>List<T></returns>
        /// <exception cref="Exception"></exception> 
        protected List<T> GetAll<T>(Action<ISheet, List<T>> action)
        {
            List<T> list = new List<T>();
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return null;
                //获得第一个Sheet页
                ISheet sheet = workbook.GetSheetAt(0);
                //获得第一行
                //IRow headerRow = sheet.GetRow(0);
                //获得总列数
                //int cellCount = headerRow.LastCellNum;
                action.Invoke(sheet, list);//传入操作
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }


        protected List<T> GetAll<T>(Action<IWorkbook, List<T>> action)
        {
            List<T> list = new List<T>();
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return null;
                action.Invoke(workbook, list);//传入操作
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }

        /// <summary>
        /// 获取Model数据,一般为选型报告表
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="action">委托传入具体读取逻辑</param>
        /// <returns>T</returns>
        /// <exception cref="Exception">Excel没读取成功</exception>
        protected T GetOne<T>(Action<ISheet, T> action) where T : new()
        {
            T model = new T();
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return default(T);
                //获得第一个Sheet页
                ISheet sheet = workbook.GetSheetAt(0);
                action.Invoke(sheet, model);//传入操作
                return model;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }


        protected T GetOne<T>(Action<IWorkbook, T> action) where T : new()
        {
            T model = new T();
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return default(T);

                action.Invoke(workbook, model);//传入操作
                return model;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }

        /// <summary>
        /// 获取Model数据,一般为选型报告表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="action"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        protected T GetValue<T>(Func<ISheet, T> action)
        {

            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return default(T);
                //获得第一个Sheet页
                ISheet sheet = workbook.GetSheetAt(0);
                var model = action.Invoke(sheet);//传入操作
                return model;
            }
            catch (Exception ex)
            {
                throw new Exception("Excel读取失败", ex);
            }
        }



        /// <summary>
        /// 根据行和列的Index获得单元格中的值
        /// </summary>
        /// <param name="row">行</param>
        /// <param name="cellNum">单元格索引</param>
        /// <returns>单元格中的值</returns>
        protected string GetCellValue(IRow row, int cellIndex)
        {
            return GetCellValueFromCellType(row.GetCell(cellIndex));
        }
        /// <summary>
        /// 根据单元格类型来获取它的值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>字符串类型的值</returns>
        private string GetCellValueFromCellType(ICell cell)
        {
            if (cell == null)
            {
                return null;
            }
            switch (cell.CellType)
            {
                //首先在NPOI中数字和日期都属于Numeric类型
                //通过NPOI中自带的DateUtil.IsCellDateFormatted判断是否为时间日期类型
                case CellType.Numeric when DateUtil.IsCellDateFormatted(cell):
                    return cell?.DateCellValue.ToString();
                case CellType.Formula:
                case CellType.Numeric:
                    //其他数字类型
                    return cell?.NumericCellValue.ToString();
                //空数据类型
                case CellType.Blank:
                    return "";
                //错误
                case CellType.Error:
                    return null;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();

                //其他类型都按字符串类型来处理（未知类型CellType.Unknown，字符串类型CellType.String）
                default:
                    return cell?.StringCellValue;

            }
        }

        /// <summary>
        /// 保存Excel,该方法传入一个匿名方法
        /// </summary>
        /// <param name="action">具体存入的内容</param>
        /// <returns>是否保存成功</returns>
        protected bool SaveExcelToCopy(Action<IWorkbook> action)
        {

            IWorkbook workbook = null;
            if (string.IsNullOrEmpty(FilePath) || !File.Exists(FilePath))
            {
                MessageBox.Show("未设置'输出模板文件'或该路径错误");
                return false;
            }

            string extension = Path.GetExtension(FilePath);

            try
            {
                /*if (File.Exists(SavePath))
                {
                    var result = MessageBox.Info("当前路径下已有该文件,是否覆盖","请选择是否覆盖文件");
                    if (result!=System.Windows.MessageBoxResult.OK)
                    {
                        return false;
                    } 
                }*/
                File.Copy(FilePath, SavePath, true);
                FileStream fs = File.Open(SavePath, FileMode.Open, FileAccess.ReadWrite);
                if (extension.Equals(".xls"))//2007以下
                {
                    workbook = new HSSFWorkbook(fs);
                }
                if (extension.Equals(".xlsx"))//2007以上
                {

                    workbook = new XSSFWorkbook(fs);
                }
                action?.Invoke(workbook);
                fs.Close();
                using (var wook = new FileStream(SavePath, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(wook);
                }
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
        }



        protected bool SaveExcelSourceFile(Action<IWorkbook> action)
        {

            IWorkbook workbook = null;
            if (string.IsNullOrEmpty(FilePath) || !File.Exists(FilePath))
            {
                MessageBox.Show("未设置'文件路径'或该路径错误");
                return false;
            }

            string extension = Path.GetExtension(FilePath);

            try
            {
                /*if (File.Exists(SavePath))
                {
                    var result = MessageBox.Info("当前路径下已有该文件,是否覆盖","请选择是否覆盖文件");
                    if (result!=System.Windows.MessageBoxResult.OK)
                    {
                        return false;
                    }
                }*/

                FileStream fs = File.Open(FilePath, FileMode.Open, FileAccess.ReadWrite);
                if (extension.Equals(".xls"))//2007以下
                {
                    workbook = new HSSFWorkbook(fs);
                }
                if (extension.Equals(".xlsx"))//2007以上
                {

                    workbook = new XSSFWorkbook(fs);
                }
                action?.Invoke(workbook);
                fs.Close();
                using (var wook = new FileStream(FilePath, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(wook);
                }
                return true;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                return false;
            }
        }

    }
}